﻿using Liu_Project.Dal.Common;
using Liu_Project.Entity.Admin;
using LSL.SqlClient;
using System;
using System.Data;
using System.Text;
using Liu_Project.Utils;

namespace Liu_Project.Dal.Admin
{
    public class EntRoleDal : DalBase
    {
        private readonly StringBuilder sb = new StringBuilder();

        /// <summary>
        /// 获取角色列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetRoleList(string sqlWhere, string sortField, string sortOrder, int page, int pageSize, ref int total)
        {
            var sqlHelper = new SQLHelper() { IsAutoClose = false };
            try
            {
                string TbRole = sqlHelper.GetTempTable();
                sb.Clear();
                sb.AppendLine("SELECT A.RoleId,A.RoleName");
                sb.AppendLine(",A.CreateTime,A.CreateUserId,B.UserNo CreateUserNo,B.UserName CreateUserName");
                sb.AppendLine(",A.UpdateTime,A.UpdateUserId,C.UserNo UpdateUserNo,C.UserName UpdateUserName");
                sb.AppendLine("INTO " + TbRole);
                sb.AppendLine("FROM EntRole A WITH(NOLOCK)");
                sb.AppendLine("LEFT JOIN EntUser B WITH(NOLOCK) ON A.CreateUserId=B.UserId");
                sb.AppendLine("LEFT JOIN EntUser C WITH(NOLOCK) ON A.UpdateUserId=C.UserId");
                sb.AppendLine("WHERE 1=1 ");
                sb.AppendLine(sqlWhere);
                sqlHelper.ExecuteNonQuery(sb.ToString());

                return sqlHelper.GetPageTableList(TbRole, sortField, sortOrder, pageSize, page, ref total);

            }
            finally
            {
                sqlHelper.Close();
            }
        }

        /// <summary>
        /// 角色编辑保存
        /// </summary>
        /// <param name="_state"></param>
        /// <param name="RoleId"></param>
        /// <param name="roleName"></param>
        public void Save(string _state, string RoleId, string roleName)
        {
            if (_state == ActionOptions.Add)
            {
                var role = new EntRole
                {
                    RoleName = roleName
                };
                SetCreateDefaultValue(role);
                InsertByEntity(role);
            }
            if (_state == ActionOptions.Edit)
            {
                var roleItem = GetEntityById<EntRole>(RoleId);
                roleItem.RoleName = roleName;
                SetUpdateDefaultValue(roleItem);
                UpdateByEntity(roleItem);
            }
        }

        /// <summary>
        /// 根据角色获取权限菜单
        /// </summary>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public DataTable GetRoleMenuList(string _type, string _Id, string _IsVisiable)
        {
            DataTable dt = null;

            new SQLHelper().TryOpen(sqlHelper =>
            {
                sb.Clear();
                if (_type == "R")
                {
                    #region 角色
                    if (CurrentUser.IsAdministrator)
                    {
                        sb.AppendLine("SELECT A.MenuId ,ISNULL(A.ParentId,0) ParentId,A.MenuName ");
                        sb.AppendLine(",CASE ISNULL(B.MenuId,0) WHEN 0 THEN '0' ELSE '1' END AS checkArr");
                        //sb.AppendLine(",CAST(CASE ISNULL(A.IsVisiable,0) WHEN 0 THEN 1 ELSE 0 END AS BIT) AS disabled");
                        sb.AppendLine("FROM dbo.EntMenu A WITH(NOLOCK)");
                        sb.AppendFormat("LEFT JOIN dbo.EntOwnerMenuCapability B ON B.MenuId = A.MenuId AND B.OwnerType='R' AND B.OwnerId='{0}' ", _Id);
                        if (_IsVisiable == "1")
                            sb.AppendLine("WHERE A.IsVisiable=1");
                        sb.AppendLine("ORDER BY A.Level ,A.OrderFlag");
                    }
                    else
                    {
                        sb.AppendLine("SELECT B.MenuId,ISNULL(B.ParentId,0) ParentId,B.MenuName");
                        sb.AppendLine(",CASE ISNULL(C.MenuId,0) WHEN 0 THEN '0' ELSE '1' END AS checkArr");
                        sb.AppendLine("FROM dbo.EntOwnerMenuCapability A ");
                        sb.AppendLine("INNER JOIN dbo.EntMenu B WITH(NOLOCK) ON B.MenuId = A.MenuId");
                        sb.AppendFormat("LEFT JOIN dbo.EntOwnerMenuCapability C ON C.MenuId = A.MenuId AND C.OwnerType='R' AND C.OwnerId='{0}' ", _Id);
                        sb.AppendLine("WHERE A.OwnerId='" + CurrentUser.EntRoleUser.RoleId + "'");
                        if (_IsVisiable == "1")
                            sb.AppendLine(" AND B.IsVisiable=1");
                        sb.AppendLine("ORDER BY B.Level ,B.OrderFlag");
                    }
                    #endregion
                }
                else if (_type == "U")
                {
                    var tbCurrRoleMenu = Common.RoleHelper.GetRoleMenu(DataConvert.ToLong(_Id), sqlHelper);//当前对应查询用户权限

                    sb.AppendLine("SELECT A.MenuId,ISNULL(A.ParentId,0) ParentId,A.MenuName");
                    sb.AppendLine(",CASE ISNULL(C.MenuId,0) WHEN 0 THEN '0' ELSE '1' END AS checkArr");
                    sb.AppendLine("FROM dbo.EntMenu A WITH(NOLOCK) ");
                    if (!CurrentUser.IsAdministrator)
                    {
                        var tbRoleMenu = Common.RoleHelper.GetRoleMenu(CurrentUser.UserId, sqlHelper);//当前登录用户权限
                        sb.AppendLine("INNER JOIN " + tbRoleMenu + " RM ON RM.MenuId = A.MenuId");
                    }
                    sb.AppendLine("LEFT JOIN " + tbCurrRoleMenu + " C ON C.MenuId = A.MenuId");
                    if (_IsVisiable == "1")
                        sb.AppendLine("WHERE A.IsVisiable=1");
                    sb.AppendLine("ORDER BY A.Level ,A.OrderFlag");
                }
                if (sb.Length > 0)
                    dt = sqlHelper.ExecuteDataTable(sb.ToString());

            });

            return dt;

        }

        /// <summary>
        /// 保存权限菜单
        /// </summary>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public void SaveMenuCapability(string _ownerType, string _Id, DataTable dt)
        {
            var sqlHelper = new SQLHelper { IsAutoClose = false };
            try
            {
                if (dt.Rows.Count == 0)
                    return;

                var TbMenuCapability = sqlHelper.DataTableToTempTable(dt);

                sqlHelper.ExecuteNonQuery("CREATE INDEX [INDEX_" + TbMenuCapability + "] ON [" + TbMenuCapability + "] (nodeId)");

                //删除原来权限菜单记录
                sb.Length = 0;
                sb.AppendFormat("DELETE EntOwnerMenuCapability WHERE OwnerType='{0}' AND OwnerId='{1}'", _ownerType, _Id);
                sqlHelper.ExecuteNonQuery(sb.ToString());

                //插入数据到临时表
                //using (System.Data.SqlClient.SqlBulkCopy sbc = new System.Data.SqlClient.SqlBulkCopy(sqlHelper.connection))
                //{
                //    sbc.BatchSize = 1000;
                //    sbc.DestinationTableName = TbMenuCapability;
                //    sbc.WriteToServer(dt);
                //}

                var PList = new SqlParameterList();
                sqlHelper.BeginTransaction();
                if (_ownerType == "R")  //通过角色权限保存
                {
                    sb.Length = 0;
                    sb.AppendLine("INSERT INTO EntOwnerMenuCapability");
                    sb.AppendLine("(OwnerType,OwnerId,MenuId,Value,CreateTime,CreateUserId,CreateUserName,UpdateTime,UpdateUserId,UpdateUserName)");
                    sb.AppendLine("SELECT @OwnerType,@OwnerId,nodeId MenuId,@Value,@CreateTime,@CreateUserId,@CreateUserName,@UpdateTime,@UpdateUserId,@UpdateUserName ");
                    sb.AppendLine("FROM " + TbMenuCapability);

                    PList.Add("OwnerId", _Id);
                    PList.Add("OwnerType", _ownerType);
                    PList.Add("Value", 1);
                    PList.Add("CreateTime", DateTime.Now);
                    PList.Add("CreateUserId", CurrentUser.UserId);
                    PList.Add("CreateUserName", CurrentUser.UserName);
                    PList.Add("UpdateTime", DateTime.Now);
                    PList.Add("UpdateUserId", CurrentUser.UserId);
                    PList.Add("UpdateUserName", CurrentUser.UserName);
                    sqlHelper.ExecuteNonQuery(sb.ToString(), PList);
                }
                else if (_ownerType == "U")  //用户特殊权限保存
                {
                    //1、找出用户所属角色
                    //2、角色权限没有，用户权限勾选到的需要添加value=1的记录
                    //3、角色权限有，用户权限也勾选到的不需要添加U的记录
                    //4、角色权限有，用户权限没勾选到的需要添加value=-1的记录

                    string TbRoleUser = sqlHelper.GetTempTable();
                    sb.Length = 0;
                    sb.AppendLine("SELECT RoleId INTO " + TbRoleUser + " FROM EntRoleUser WHERE UserId=" + _Id);
                    sqlHelper.ExecuteNonQuery(sb.ToString());

                    string TbInsertMenu = sqlHelper.GetTempTable();
                    sb.Length = 0;
                    sb.AppendLine("SELECT 'U' AS OwnerType," + _Id + " AS OwnerId,A.nodeId MenuId,1 AS [Value]");
                    sb.AppendLine("INTO " + TbInsertMenu);
                    sb.AppendLine("FROM " + TbMenuCapability + " A");
                    sb.AppendLine("WHERE NOT EXISTS (SELECT 1 FROM EntOwnerMenuCapability B");
                    sb.AppendLine("                  INNER JOIN " + TbRoleUser + " RU ON RU.RoleId = B.OwnerId");
                    sb.AppendLine("                  WHERE A.nodeId=B.MenuId AND B.OwnerType='R')");
                    sqlHelper.ExecuteNonQuery(sb.ToString());

                    sb.Length = 0;
                    sb.AppendLine("INSERT INTO EntOwnerMenuCapability");
                    sb.AppendLine("(OwnerType,OwnerId,MenuId,Value,CreateTime,CreateUserId,CreateUserName,UpdateTime,UpdateUserId,UpdateUserName)");
                    sb.AppendLine("SELECT OwnerType,OwnerId,MenuId,Value,@CreateTime,@CreateUserId,@CreateUserName,@UpdateTime,@UpdateUserId,@UpdateUserName ");
                    sb.AppendLine("FROM " + TbInsertMenu);

                    PList.Add("CreateTime", DateTime.Now);
                    PList.Add("CreateUserId", CurrentUser.UserId);
                    PList.Add("CreateUserName", CurrentUser.UserName);
                    PList.Add("UpdateTime", DateTime.Now);
                    PList.Add("UpdateUserId", CurrentUser.UserId);
                    PList.Add("UpdateUserName", CurrentUser.UserName);
                    sqlHelper.ExecuteNonQuery(sb.ToString(), PList);

                    TbInsertMenu = sqlHelper.GetTempTable();
                    sb.Length = 0;
                    sb.AppendLine("SELECT 'U' AS OwnerType," + _Id + " AS OwnerId,A.MenuId,-1*COUNT(A.MenuId) AS [Value]");
                    sb.AppendLine("INTO " + TbInsertMenu);
                    sb.AppendLine("FROM EntOwnerMenuCapability A");
                    sb.AppendLine("INNER JOIN " + TbRoleUser + " RU ON RU.RoleId = A.OwnerId");
                    sb.AppendLine("WHERE A.OwnerType='R'");
                    sb.AppendLine("AND NOT EXISTS (SELECT 1 FROM " + TbMenuCapability + " B WHERE A.MenuId=B.nodeId )");
                    sb.AppendLine("GROUP BY A.MenuId");
                    sqlHelper.ExecuteNonQuery(sb.ToString());

                    sb.Length = 0;
                    sb.AppendLine("INSERT INTO EntOwnerMenuCapability");
                    sb.AppendLine("(OwnerType,OwnerId,MenuId,Value,CreateTime,CreateUserId,CreateUserName,UpdateTime,UpdateUserId,UpdateUserName)");
                    sb.AppendLine("SELECT OwnerType,OwnerId,MenuId,Value,@CreateTime,@CreateUserId,@CreateUserName,@UpdateTime,@UpdateUserId,@UpdateUserName ");
                    sb.AppendLine("FROM " + TbInsertMenu);

                    PList.Clear();
                    PList.Add("CreateTime", DateTime.Now);
                    PList.Add("CreateUserId", CurrentUser.UserId);
                    PList.Add("CreateUserName", CurrentUser.UserName);
                    PList.Add("UpdateTime", DateTime.Now);
                    PList.Add("UpdateUserId", CurrentUser.UserId);
                    PList.Add("UpdateUserName", CurrentUser.UserName);
                    sqlHelper.ExecuteNonQuery(sb.ToString(), PList);
                }
                sqlHelper.CommitTransaction();
            }
            catch (Exception)
            {
                sqlHelper.RollbackTransaction();
                throw;
            }
            finally
            {
                sqlHelper.Close();
            }
        }

        /// <summary>
        /// 根据角色获取用户列表
        /// </summary>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public DataTable GetRoleUserList(string roleId, string sortField, string sortOrder, int page, int pageSize, ref int total)
        {
            var sqlHelper = new SQLHelper { IsAutoClose = false };
            try
            {
                string TbRoleUser = sqlHelper.GetTempTable();
                sb.Clear();
                sb.AppendLine("SELECT B.*,A.RoleName,U.UserNo,U.UserName");
                sb.AppendLine(",D.UserNo CreateUserNo,C.UserNo UpdateUserNo");
                sb.AppendLine("INTO " + TbRoleUser);
                sb.AppendLine("FROM dbo.EntRole A");
                sb.AppendLine("INNER JOIN dbo.EntRoleUser B ON B.RoleId = A.RoleId");
                sb.AppendLine("INNER JOIN dbo.EntUser U ON U.UserId = B.UserId");
                sb.AppendLine("LEFT JOIN EntUser D ON A.CreateUserId=D.UserId");
                sb.AppendLine("LEFT JOIN EntUser C ON A.UpdateUserId=C.UserId");
                sb.AppendFormat(" WHERE A.RoleId='{0}'", roleId);
                sqlHelper.ExecuteNonQuery(sb.ToString());

                return sqlHelper.GetPageTableList(TbRoleUser, sortField, sortOrder, pageSize, page, ref total);
            }
            finally
            {
                sqlHelper.Close();
            }
        }

        /// <summary>
        /// 根据角色获取用户列表
        /// </summary>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public DataTable GetUserRoleList(string Id)
        {
            sb.Clear();
            sb.AppendLine("SELECT A.RoleId value,A.RoleName name");
            sb.AppendLine(",CAST(CASE ISNULL(B.RoleId,0) WHEN 0 THEN '0' ELSE '1' END AS BIT) AS selected");
            sb.AppendLine("FROM dbo.EntRole A ");
            sb.AppendFormat("LEFT JOIN dbo.EntRoleUser B ON B.RoleId = A.RoleId AND B.UserId='{0}' ", Id);
            return new SQLHelper().ExecuteDataTable(sb.ToString());
        }

    }
}
